import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.logging.Logger;

import com.google.gson.Gson;


public class jdbc {
	
	Gson gson = null;
	
	Connection con = null;
	Statement st = null;
	ResultSet rs = null;
	
	String internal_Query = null;		
	ResultSet internal_rs = null;
	Statement internal_st = null;
	
	String tag_Query = null;
	ResultSet tag_rs = null;
	Statement tag_st = null;
	

	
	String url = "jdbc:postgresql://localhost/osm";
	String user = "snake";
	String password = "osm";
	

	/**
	 * @param args
	 */
	
	public static void main(String[] args){
		//new jdbc().queries();
		new jdbc().zoomInsertion();
	}
	
	public jdbc(){
		
		gson = new Gson();
		
	}
	
	public void queries() {
		
		String insertion_Query = null;
		ResultSet insertion_rs = null;
		Statement insertion_st = null;
		


		try{
			con = DriverManager.getConnection(url, user, password);
			System.out.println("conneciton established");
			st = con.createStatement();

			
			//String query = "UPDATE OR INSERT INTO nitesh_maps_arrays VALUES(2,'"+payload+"');";
			String query = "SELECT st_x, st_y, way_id, sequence_id FROM nitesh_maps;";
			
			/*GETS DATA*/
			
			System.out.println(query);
			
			//rs = st.executeQuery("SELECT VERSION()");
			
			rs = st.executeQuery(query);
			
			
			/*
			
			
			rs.next();

					
			internal_Query = "SELECT COUNT(*) FROM nitesh_maps WHERE way_id="+Integer.toString(rs.getInt(3))+";";
			internal_rs = internal_st.executeQuery(internal_Query);
			internal_rs.next();
			System.out.println("internal " + internal_rs.getString(1));
			
			*/
			internal_st = con.createStatement();
			insertion_st = con.createStatement();
			tag_st = con.createStatement();
			
			rs.next(); //RS is used so each value from nitesh_maps is looked over
			
			while(true){
				
				int sequence_length = 0;
				//float[][] float_payload;
				int[][] int_payload;
				HashMap<String, String> tags;
				//double[][] double_payload;
				String string_payload;
				
				/*FINDS NUMBER OF NODES IN EACH WAY*/
				internal_Query = "SELECT COUNT(*) FROM nitesh_maps WHERE way_id="+Integer.toString(rs.getInt(3))+";";
				internal_rs = internal_st.executeQuery(internal_Query);
				internal_rs.next();
				sequence_length = internal_rs.getInt(1);
				
				
				/*TAGS MAP */
				
				tag_Query = "SELECT COUNT(*) FROM way_tags WHERE way_id="+Integer.toString(rs.getInt(3))+";";
				tag_rs = tag_st.executeQuery(tag_Query);
				tag_rs.next();
				
				int tag_count = tag_rs.getInt(1);				
				System.out.println("count " + tag_count);
				
				tag_Query = "SELECT k, v FROM way_tags WHERE way_id="+Integer.toString(rs.getInt(3))+";";
				tag_rs = tag_st.executeQuery(tag_Query);
				tag_rs.next();
				
				tags = new HashMap<String, String>();
				
				for(int i = 0; i<tag_count; i++){
					System.out.println("key " + tag_rs.getString(1) + " value "+ tag_rs.getString(2));
					tags.put(tag_rs.getString(1), tag_rs.getString(2));
					
					tag_rs.next();
					
						}
				
				
				System.out.println("hashmap size "+tags.size());
				
				/*TAGS MAP end*/
				
				System.out.println("internal " + sequence_length);

				int_payload = new int[sequence_length][2];
								
				String way_id = Integer.toString(rs.getInt(3));
				System.out.println("Way id = " + Integer.toString(rs.getInt(3)));
				
				
			for(int i = 0; i<sequence_length; i++){				
				System.out.println("row X " + Integer.toString(i)+ " " +  rs.getDouble(1) + " row Y " + Integer.toString(i)+ " " +rs.getDouble(2));
				int_payload[i][0] = rs.getInt(1);
				int_payload[i][1] = rs.getInt(2);

				rs.next();
							}//END OF FOR LOOP - CREATES WAY ARRAY OF NODES
			
			/** INSERTION - INSERTS ARRAY POPULATED WITH WAY NODES INTO 

 *
 */
			
			
			string_payload = gson.toJson(int_payload);
			System.out.println(string_payload);
			
			String tags_payload = gson.toJson(tags);
			System.out.println(tags_payload);
			
			pathTagCombined full_payload = new pathTagCombined(int_payload, tags);
			String full_payload_string = gson.toJson(full_payload);
			System.out.println(full_payload_string);
			
			full_payload = gson.fromJson(full_payload_string, pathTagCombined.class);
						
			try{ 
			insertion_Query = "INSERT INTO nitesh_maps_arrays VALUES("+way_id+",'"+string_payload+"','"+full_payload_string+"');";
			internal_st.executeQuery(insertion_Query);			
			System.out.println(way_id + " successfully added");}
			catch(SQLException ex){System.out.println(ex);}
			finally{}
			
			/** INSERTION END */
			
			
			
			}// END OF INFINITE LOOP
			
		}//END OF TRY
		
		catch(SQLException ex){
			//Logger lgr = Logger.getLogger(Version.class.getName());
	
			if(ex.toString().equals("org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.")){
				System.out.println("Complete");
			}
			
			System.out.println(ex);
		}
		finally{
			try{
				if(rs != null){
					rs.close();
				}
				if(st != null){
					st.close();
				}
				if(con != null){
					con.close();
				}
				
				
			}
			catch(SQLException ex){	System.out.println("error closing");}
			
			
		}
		
		

	}

	public ResultSet acquire(){
		/**
		 * Update for Lon lat ranges soon
		 */
		
		String query = "SELECT float_array FROM nitesh_maps_arrays;";
		
		try {
			
			con = DriverManager.getConnection(url, user, password);
			System.out.println("conneciton established");
			st = con.createStatement();
			
			
			rs = st.executeQuery(query);
			
			return rs;
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
		
		
	}
	
	
	public int zoomDetermine(HashMap<String,String> map){
		
		int level = 1;
		
		/*
		 * LVL 1 - everything
		 * LVL2 - all highways and natural
		 * LVL3 - natual and non resedential highways
		 *  USER PICKS ZOOM LEVEL AND SHOW EVERYTHING ON HIGHER LEVELS
		 * 
		 */
		
		if(map.containsKey("natural")){
			level = 3;
			//System.out.println("natural formation");
		}
		
		else if(map.containsKey("rail") || map.containsKey("railway")){
			level = 3;
			
		}
		
		if(map.containsKey("highway")){
			level = 2;
			
			String highwayType = map.get("highway");
			
			if(highwayType.equals("primary") || highwayType.equals("motorway") || highwayType.equals("trunk")){
				level = 3;
			}
			
			
		}
		
		return level;
		
	}
	
	
	
	public ResultSet acquireCombined(){
		/**
		 * Update for Lon lat ranges soon
		 */
		
		String query = "SELECT full_payload FROM nitesh_maps_arrays;";
		
		try {
			
			con = DriverManager.getConnection(url, user, password);
			System.out.println("conneciton established");
			st = con.createStatement();
			
			
			rs = st.executeQuery(query);
			
			return rs;
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
		
		
	}
	
	public ResultSet acquireFilter(int x, int y, int zoom){
		/**
		 * Update for HIGHWAYS AT CERTAIN ZOOM
		 * 
		 */
		
		int range = (int) (1000 * zoom); //1000 CONSTANT TIMES ZOOM
		
		//TESTED WORKING!
		
		int highest = y+range;
		int lowest = y-range;
		int leftMost = x-range;
		int rightMost = x+range;
		
		
		String query = "SELECT full_payload from nitesh_maps_zoom WHERE way_id IN(SELECT way_id FROM nitesh_maps WHERE st_y<"+highest+" AND st_y>"+lowest+" AND st_x>"+leftMost+" AND st_x<"+rightMost+")"+ "AND zoom>="+zoom+";";
		
		
		//String query = "SELECT full_payload from nitesh_maps_zoom WHERE way_id IN(SELECT way_id FROM nitesh_maps WHERE st_y<6718569 AND st_y>6714569 AND st_x>-15905 AND st_x<-11905) AND zoom >=2;";
		//String query = "SELECT full_payload from nitesh_maps_zoom WHERE zoom>=2 LIMIT 700;";
		
		
		System.out.println(query);;
		
		try {
			
			con = DriverManager.getConnection(url, user, password);
			System.out.println("conneciton established");
			st = con.createStatement();
			
			
			rs = st.executeQuery(query);
			
			return rs;
			
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rs;
		
		
	}
	
	
public void zoomInsertion() {
		
		String insertion_Query = null;
		ResultSet insertion_rs = null;
		Statement insertion_st = null;
		
		int zoomLevel = 0;
		


		try{
			con = DriverManager.getConnection(url, user, password);
			System.out.println("conneciton established");
			st = con.createStatement();

			
			//String query = "UPDATE OR INSERT INTO nitesh_maps_arrays VALUES(2,'"+payload+"');";
			String query = "SELECT st_x, st_y, way_id, sequence_id FROM nitesh_maps;";
			
			/*GETS DATA*/
			
			System.out.println(query);
			
			//rs = st.executeQuery("SELECT VERSION()");
			
			rs = st.executeQuery(query);
			
			
			/*
			
			
			rs.next();

					
			internal_Query = "SELECT COUNT(*) FROM nitesh_maps WHERE way_id="+Integer.toString(rs.getInt(3))+";";
			internal_rs = internal_st.executeQuery(internal_Query);
			internal_rs.next();
			System.out.println("internal " + internal_rs.getString(1));
			
			*/
			internal_st = con.createStatement();
			insertion_st = con.createStatement();
			tag_st = con.createStatement();
			
			rs.next(); //RS is used so each value from nitesh_maps is looked over
			
			while(true){
				
				int sequence_length = 0;
				//float[][] float_payload;
				int[][] int_payload;
				HashMap<String, String> tags;
				//double[][] double_payload;
				String string_payload;
				
				/*FINDS NUMBER OF NODES IN EACH WAY*/
				internal_Query = "SELECT COUNT(*) FROM nitesh_maps WHERE way_id="+Integer.toString(rs.getInt(3))+";";
				internal_rs = internal_st.executeQuery(internal_Query);
				internal_rs.next();
				sequence_length = internal_rs.getInt(1);
				
				
				/*TAGS MAP */
				
				tag_Query = "SELECT COUNT(*) FROM way_tags WHERE way_id="+Integer.toString(rs.getInt(3))+";";
				tag_rs = tag_st.executeQuery(tag_Query);
				tag_rs.next();
				
				int tag_count = tag_rs.getInt(1);				
				System.out.println("count " + tag_count);
				
				tag_Query = "SELECT k, v FROM way_tags WHERE way_id="+Integer.toString(rs.getInt(3))+";";
				tag_rs = tag_st.executeQuery(tag_Query);
				tag_rs.next();
				
				tags = new HashMap<String, String>();
				
				for(int i = 0; i<tag_count; i++){
					System.out.println("key " + tag_rs.getString(1) + " value "+ tag_rs.getString(2));
					tags.put(tag_rs.getString(1), tag_rs.getString(2));
					
					tag_rs.next();
					
						}
				
				
				System.out.println("hashmap size "+tags.size());
				zoomLevel = zoomDetermine(tags);
				
				
				/*TAGS MAP end*/
				
				System.out.println("internal " + sequence_length);

				int_payload = new int[sequence_length][2];
								
				String way_id = Integer.toString(rs.getInt(3));
				System.out.println("Way id = " + Integer.toString(rs.getInt(3)));
				
				
			for(int i = 0; i<sequence_length; i++){				
				System.out.println("row X " + Integer.toString(i)+ " " +  rs.getDouble(1) + " row Y " + Integer.toString(i)+ " " +rs.getDouble(2));
				int_payload[i][0] = rs.getInt(1);
				int_payload[i][1] = rs.getInt(2);

				rs.next();
							}//END OF FOR LOOP - CREATES WAY ARRAY OF NODES
			
			/** INSERTION - INSERTS ARRAY POPULATED WITH WAY NODES INTO NITESH_MAPS_ARRAYS */
			
			
			string_payload = gson.toJson(int_payload);
			System.out.println(string_payload);
			
			String tags_payload = gson.toJson(tags);
			System.out.println(tags_payload);
			
			pathTagCombined full_payload = new pathTagCombined(int_payload, tags);
			String full_payload_string = gson.toJson(full_payload);
			System.out.println(full_payload_string);
			
			full_payload = gson.fromJson(full_payload_string, pathTagCombined.class);
						
			try{ 
			insertion_Query = "INSERT INTO nitesh_maps_zoom VALUES("+way_id+",'"+full_payload_string+"','"+zoomLevel+"');";
			internal_st.executeQuery(insertion_Query);			
			System.out.println(way_id + " successfully added");}
			catch(SQLException ex){System.out.println(ex);}
			finally{}
			
			/** INSERTION END */
			
			
			
			}// END OF INFINITE LOOP
			
		}//END OF TRY
		
		catch(SQLException ex){
			//Logger lgr = Logger.getLogger(Version.class.getName());
	
			if(ex.toString().equals("org.postgresql.util.PSQLException: ResultSet not positioned properly, perhaps you need to call next.")){
				System.out.println("Complete");
			}
			
			System.out.println(ex);
		}
		finally{
			try{
				if(rs != null){
					rs.close();
				}
				if(st != null){
					st.close();
				}
				if(con != null){
					con.close();
				}
				
				
			}
			catch(SQLException ex){	System.out.println("error closing");}
			
			
		}
	}

}
